
use chrono::Local;
use sqlx::mysql::{MySqlRow,MySqlQueryResult,MySqlConnection,MySqlPoolOptions,MySqlConnectOptions};
use sqlx::pool::PoolConnection;
use sqlx::{Acquire, Executor, MySql, Pool};
use sqlx::ConnectOptions;
use sqlx_mysql::MySqlArguments;
use tracing::{error, info, warn ,debug};
use std::collections::{HashMap};
use std::str::FromStr;
use std::sync::{Arc, RwLock};
use std::time::Duration;
use serde::{Deserialize, Serialize};
use sqlx_core::from_row::FromRow;

use crate::error::{Result,Error};
use crate::conf;
pub type ExecuteResult = Result<MySqlQueryResult>;
type LinkPool = Pool<MySql>;

#[derive(Debug,Clone,Deserialize, Serialize)]
pub struct ConnectPoolConfig{
    pub username: String,
    pub password: String,
    pub host: String,
    pub port:u16,
    pub charset: Option<String>,
    pub timezone: Option<String>,
    pub database_name: Option<String>,
    pub max_connections: Option<u32>,// 最大连接数
    pub min_connections: Option<u32>,// 最小连接数
    pub acquire_timeout: Option<u32>,// 获取连接超时
    pub idle_timeout: Option<u32>,// 获取连接超时
    pub max_lifetime: Option<u32>,// 获取连接超时
}

impl Default for ConnectPoolConfig {
    fn default() -> Self {
        Self{
            username:"root".to_owned(),
            password:"zhunian1218".to_owned(),
            host:"127.0.0.1".to_owned(),
            port:3306,
            charset: Some("utf8mb4".to_owned()),
            timezone: Some("+08:00".to_owned()),
            database_name: Some("test".to_owned()),
            max_connections: Some(20),
            min_connections: Some(5),// 最小连接数
            acquire_timeout: None,// 超时  毫秒
            idle_timeout: None,// 超时  毫秒
            max_lifetime: None,// 超时  毫秒
        }
    }
}

impl From<&ConnectPoolConfig> for MySqlConnectOptions {
    fn from(value: &ConnectPoolConfig) -> Self {
        let mut opt = MySqlConnectOptions::default();
        opt = opt
        .username(&value.username)
        .password(&value.password)
        .host(&value.host)
        .port(value.port);

        if let Some(t) = &value.database_name {
            opt = opt.database(t);
        }

        if let Some(t) = &value.charset {
            opt = opt.charset(t);
        }

        if let Some(t) = &value.timezone {
            opt = opt.timezone(Some(t.clone()));
        }
        opt
    }    
}

impl From<&ConnectPoolConfig> for MySqlPoolOptions {
    fn from(value: &ConnectPoolConfig) -> Self {
        let mut opt = MySqlPoolOptions::default();
        if let Some(x) = value.max_connections {
            opt = opt.max_connections(x);
        }

        if let Some(x) = value.min_connections {
            opt = opt.min_connections(x);
        }

        if let Some(x) = value.acquire_timeout {
            opt = opt.acquire_timeout(Duration::from_millis(x as u64));
        }

        if let Some(x) = value.idle_timeout {
            opt = opt.idle_timeout(Duration::from_millis(x as u64));
        }

        if let Some(x) = value.max_lifetime {
            opt = opt.max_lifetime(Duration::from_millis(x as u64));
        }
        opt
    }    
}

impl ConnectPoolConfig{
    pub fn get_pool(&self) -> LinkPool {
        let connect_options: MySqlConnectOptions = self.into();
        let pool_options: MySqlPoolOptions = self.into();
        pool_options.connect_lazy_with(connect_options)
    }

    pub async fn get_single_link(&self) -> Result<MySqlConnection>{    
        let connect_options: MySqlConnectOptions = self.into();
        connect_options.connect().await.map_err(Error::Sqlx)
    }
}

#[derive(Debug,Clone)]
pub struct DbLink{
    pool: Arc<LinkPool>
}

impl DbLink {
    
    pub fn new(pool:LinkPool) -> Self{
        Self{
            pool: Arc::new(pool)
        }
    }

    pub async fn get_pool_connection(&self) -> Result<PoolConnection<MySql>> {
        let data = self.pool.clone();
        let start = Local::now();
        loop {
            let res = data.acquire().await;
            if let Ok(link) = res {
                return Ok(link);
            }
            else{
                let now = Local::now();
                error!("get db link failed. already used {}",(now - start).num_milliseconds());
            }
        }
        //Ok(data.acquire().await?)
    }
  
    pub fn add_connect_pool(name: &str,config: &ConnectPoolConfig) -> Result<()> {
        debug!("add connect for db {} with config: {:?}",name,config);
        let _ref = GLOBAL_DB_HANDLE_MAP.clone();
        let mut guard_set = _ref.write().map_err( |e|  Error::LockError(format!("{:?}",e)))?;
        let pool = config.get_pool();
        guard_set.insert(format!("{}",name), Arc::new(Self::new(pool)));
        Ok(())
    }

    fn try_get_inner(name: &str) -> Result<Arc<Self>> {
        let _ref = GLOBAL_DB_HANDLE_MAP.clone();
        let guard = _ref.read().map_err( |e|  Error::LockError(format!("{:?}",e)))?;
        let n = guard.get(name);
        match n {
            Some(x) => {
                let r = x.clone();             
                Ok(r)
            },
            None => Err(Error::DataIsEmpty("empty util add".to_owned())),
        }
    }

    pub fn instance(name: &str) -> Result<Arc<Self>> {        
        let _res = Self::try_get_inner(name);
        if _res.is_err() { // 尝试插入一次
            let config = conf::get_db(name)?;
            let _ = Self::add_connect_pool(name,&config)?;
            return Self::try_get_inner(name);
        }
        _res
    }

    pub async fn execute_no_cache(&self,sql: &str) -> ExecuteResult
    { 
        info!("do execute_no_cache with sql: {}",sql);
        let mut conn = self.get_pool_connection().await?; 
        let mut query = sqlx::query(sql);
        query = query.persistent(false);
        query.execute(&mut *conn).await.map_err(Error::Sqlx)
    }


    pub async fn execute<A>(&self,sql: &str,bind:A) -> ExecuteResult
    where 
    A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;       
        sqlx::query_with(sql,bind.into()).execute(&mut *conn).await.map_err(Error::Sqlx)
    }

  
    pub async fn query<A>(&self,sql: &str,bind:A) ->  Result<MySqlRow>
    where 
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;
        sqlx::query_with(sql,bind.into()).fetch_one(&mut *conn).await.map_err(Error::Sqlx)        
    }

    pub async fn query_as<'a, O,A>(&self,sql: &str,bind:A) -> Result<O>
    where 
        O: Send + Unpin + for<'r> FromRow<'r, <MySql as sqlx::Database>::Row>,
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;
        sqlx::query_as_with::<MySql,O,_>(sql,bind.into()).fetch_one(&mut *conn).await.map_err(Error::Sqlx)
    }


    pub async fn query_optional<A>(&self,sql: &str, args:A) -> Result<Option<MySqlRow>>
    where        
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;
        sqlx::query_with(sql,args.into()).fetch_optional(&mut *conn).await.map_err(Error::Sqlx)
        
    }

    pub async fn query_optional_as<'a,O,A>(&self,sql: &str, args:A) ->  Result<Option<O>>
        where        
        O: Send + Unpin + for<'r> FromRow<'r, <MySql as sqlx::Database>::Row>,
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;   
        sqlx::query_as_with::<MySql,O,_>(sql,args.into()).fetch_optional(&mut *conn).await.map_err(Error::Sqlx)
    }

    pub async fn query_all<A>(&self,sql: &str,args:A) ->  Result<Vec<MySqlRow>>
    where        
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;
        sqlx::query_with(sql,args.into()).fetch_all(&mut *conn).await.map_err(Error::Sqlx)          
    }

    pub async fn query_all_as<'a,O,A>(&self,sql: &str, args:A) -> Result<Vec<O>>
    where        
        O: Send + Unpin + for<'r> FromRow<'r, <MySql as sqlx::Database>::Row>,
        A: Into<MySqlArguments>,
    {
        let mut conn = self.get_pool_connection().await?;
        sqlx::query_as_with::<MySql,O,_>(sql,args.into()).fetch_all(&mut *conn).await.map_err(Error::Sqlx)
    }
}

#[derive(Debug)]
pub struct DatabaseHandle{
    pub name: String,
    pub connect_config: Option<ConnectPoolConfig>,
    pub link_pool: RwLock<Arc<LinkPool>>,
}


lazy_static::lazy_static!{
    static ref GLOBAL_DB_HANDLE_MAP: Arc<RwLock<HashMap<String,Arc<DbLink>>>> = Arc::new(RwLock::new({
        HashMap::new()
    }));
}

pub async fn get_single_link(name:&String) -> Result<MySqlConnection>{
    let config = conf::get_db(name)?;
    config.get_single_link().await
}

// pub async fn get_link(name:&String) -> Result<PoolConnection<MySql>> {
//     let pool = get_link_pool(name)?;   
//     Ok(pool.acquire().await?)
// }

